Database partitioning enables a database table to be divided into smaller pieces, also referred to as “partitions.” Partitioning is often applied to large tables and associated indices since it allows them to be broken into smaller and more manageable pieces. A database table can be partitioned in a number of ways. Horizontal partitioning, for example, involves storing each row in a particular partition based on a respective column value. Vertical partitioning, on the other hand, involves creating partitions with fewer columns and using additional partitions to store the remaining columns. In a distributed database system, table partitions can be distributed among multiple computer hosts, which are also referred to as “nodes”. Each node provides user-level access to its partitions for query purposes. In principle, this increases overall database performance since transactional workloads can be distributed among the nodes, and can target those partitions most pertinent to a particular transaction.
Horizontal partitioning can be implemented on the basis of a partitioning policy that defines at least one partition key and multiple criteria. The partition key is a column containing the values which will form the basis of the partitioning; these values are referred to as key values. Each of the criteria defines a condition and a partition identifier. A database record, represented by a row in the database table, that satisfies a particular criterion is stored in a partition corresponding to the partition identifier associated with the satisfied criterion. The criteria that form part of a partitioning policy can define a variety of different partitioning schemes, including range partitioning (in which records having key values falling within a defined range are partitioned together), list partitioning (in which records having listed key values are partitioned together), hash partitioning (in which records having a key value hash are partitioned together), and combinations of the foregoing. While different partitioning policies define different ways of partitioning a database table, each achieves the same thing in a general sense: each ensures that a particular database record is maintained in an appropriate partition, and that subsequent queries can locate and perform database operations using that record.
These concepts can be illustrated with reference to horizontal partitioning of a simple database table that contains a list of given names and a gender for each name. The columns of such a database table are “name” and “gender.” This database table can be divided into two partitions, one that contains male names and is identified by partition identifier “MN”, and one that contains female names and is identified by partition identifier “FN”. This partitioning can be accomplished based on a partitioning policy that defines the “gender” column as the partition key, and that further defines two criteria. One criterion is, “Store records having ‘gender’=‘male’ in the partition corresponding to partition identifier ‘MN’”. Another criterion is, “Store records having ‘gender’=‘female’ in the partition corresponding to partition identifier ‘FN’”. Applying this partitioning policy to the database table will result in the records comprising the database table to be divided into a first partition containing only male names, and a second partition containing only female names.
These and other features of the present embodiments will be understood better by reading the following detailed description, taken together with the figures herein described. The accompanying drawings are not intended to be drawn to scale. In the drawings, each identical or nearly identical component that is illustrated in various figures is represented by a like numeral. For purposes of clarity, not every component may be labeled in every drawing.